const mysql = require('mysql2/promise');

async function updateMapAreasParentIds () {
  const connection = await mysql.createConnection({
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    timezone: '+08:00'
  });

  try {
    console.log('🔧 根据WebSocket数据更新地图区域父ID关系...\n');

    // 根据WebSocket通信数据定义的层级关系
    const hierarchyData = {
      // 魏国 (areaId: 100)
      100: {
        name: '魏国',
        children: {
          289: { name: '许昌', children: [2, 301, 487, 533] }, // 新手村、陈留、九里山、芒砀山
          302: { name: '洛阳', children: [285, 286, 333, 337] }, // 官渡、虎牢关、乌巢、函谷关
          304: { name: '长安', children: [303, 311, 414] } // 华山、潼关、五丈原
        }
      },
      // 蜀国 (areaId: 19)
      19: {
        name: '蜀国',
        children: {
          70: { name: '成都', children: [93, 112, 113] }, // 落凤坡、广汉、峨眉山
          111: { name: '南中', children: [52, 143, 351] }, // 云南、泸水、西洱湖
          117: { name: '汉中', children: [306, 307, 308] } // 子午谷、街亭、上方谷
        }
      },
      // 吴国 (areaId: 43)
      43: {
        name: '吴国',
        children: {
          154: { name: '建业', children: [267] }, // 广陵
          212: { name: '长沙', children: [220, 221, 222] }, // 巴陵、洞庭湖、湘水
          264: { name: '江陵', children: [137, 161, 163] } // 麦城、长坂坡、夷陵
        }
      }
    };

    // 等级信息映射
    const levelInfo = {
      2: '(1级)', 301: '(5级)', 487: '(20级)', 533: '(30级)',
      285: '(40级)', 286: '(50级)', 333: '(80级)', 337: '(55级)',
      303: '(90级)', 311: '(75级)', 414: '(65级)',
      93: '(30级)', 112: '(40级)', 113: '(50级)',
      52: '(60级)', 143: '(70级)', 351: '(80级)',
      306: '(90级)', 307: '(100级)', 308: '(120级)',
      222: '(130级)', 137: '(140级)', 161: '(140级)'
    };

    console.log('📝 步骤1: 确保顶级区域存在...');

    // 确保顶级区域存在
    for (const [areaId, areaData] of Object.entries(hierarchyData)) {
      const [existing] = await connection.execute(
        'SELECT id, name, parent_id FROM map_areas WHERE id = ?',
        [areaId]
      );

      if (existing.length === 0) {
        // 创建顶级区域
        await connection.execute(
          'INSERT INTO map_areas (id, name, parent_id, extension, createdAt, updatedAt) VALUES (?, ?, NULL, NULL, NOW(), NOW())',
          [areaId, areaData.name]
        );
        console.log(`  ✅ 创建顶级区域: ${areaData.name} (ID: ${areaId})`);
      } else {
        // 更新现有区域为顶级区域
        await connection.execute(
          'UPDATE map_areas SET name = ?, parent_id = NULL, updatedAt = NOW() WHERE id = ?',
          [areaData.name, areaId]
        );
        console.log(`  🔄 更新顶级区域: ${areaData.name} (ID: ${areaId})`);
      }
    }

    console.log('\n📝 步骤2: 设置二级城市父级关系...');

    // 设置二级城市（如许昌、洛阳等）的父级关系
    for (const [areaId, areaData] of Object.entries(hierarchyData)) {
      for (const [cityId, cityData] of Object.entries(areaData.children)) {
        const [existing] = await connection.execute(
          'SELECT id, name, parent_id FROM map_areas WHERE id = ?',
          [cityId]
        );

        if (existing.length > 0) {
          await connection.execute(
            'UPDATE map_areas SET parent_id = ?, updatedAt = NOW() WHERE id = ?',
            [areaId, cityId]
          );
          console.log(`  🔄 设置父级关系: ${cityData.name} (ID: ${cityId}) -> 父级: ${areaData.name} (ID: ${areaId})`);
        } else {
          console.log(`  ⚠️ 城市不存在: ${cityData.name} (ID: ${cityId})`);
        }
      }
    }

    console.log('\n📝 步骤3: 设置三级区域父级关系...');

    // 设置三级区域（如新手村、陈留等）的父级关系
    for (const [areaId, areaData] of Object.entries(hierarchyData)) {
      for (const [cityId, cityData] of Object.entries(areaData.children)) {
        for (const childId of cityData.children) {
          const [existing] = await connection.execute(
            'SELECT id, name, parent_id FROM map_areas WHERE id = ?',
            [childId]
          );

          if (existing.length > 0) {
            await connection.execute(
              'UPDATE map_areas SET parent_id = ?, updatedAt = NOW() WHERE id = ?',
              [cityId, childId]
            );
            const childName = existing[0].name;
            console.log(`  🔄 设置父级关系: ${childName} (ID: ${childId}) -> 父级: ${cityData.name} (ID: ${cityId})`);
          } else {
            console.log(`  ⚠️ 子区域不存在: ID ${childId}`);
          }
        }
      }
    }

    console.log('\n📝 步骤4: 设置等级信息...');

    // 设置等级信息
    for (const [areaId, level] of Object.entries(levelInfo)) {
      try {
        await connection.execute(
          'UPDATE map_areas SET extension = ?, updatedAt = NOW() WHERE id = ?',
          [level, areaId]
        );
        console.log(`  ✅ 设置等级信息: 区域 ${areaId} -> ${level}`);
      } catch (error) {
        console.log(`  ⚠️ 设置等级信息失败: 区域 ${areaId} - ${error.message}`);
      }
    }

    console.log('\n🔍 步骤5: 验证更新结果...');

    // 验证更新结果
    const [stats] = await connection.execute(`
      SELECT 
        COUNT(*) as total_areas,
        COUNT(parent_id) as areas_with_parent,
        COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as top_level_areas,
        COUNT(extension) as areas_with_extension
      FROM map_areas
    `);

    console.log('📊 区域统计:');
    console.log(`  总区域数: ${stats[0].total_areas}`);
    console.log(`  有父级区域数: ${stats[0].areas_with_parent}`);
    console.log(`  顶级区域数: ${stats[0].top_level_areas}`);
    console.log(`  有等级信息区域数: ${stats[0].areas_with_extension}`);

    // 显示层级关系验证
    console.log('\n📊 层级关系验证:');
    for (const [areaId, areaData] of Object.entries(hierarchyData)) {
      console.log(`\n${areaData.name} (ID: ${areaId}):`);

      for (const [cityId, cityData] of Object.entries(areaData.children)) {
        const [cityInfo] = await connection.execute(
          'SELECT id, name, parent_id, extension FROM map_areas WHERE id = ?',
          [cityId]
        );

        if (cityInfo.length > 0) {
          const city = cityInfo[0];
          const cityName = city.extension ? `${city.name}${city.extension}` : city.name;
          console.log(`  └─ ${cityName} (ID: ${cityId}, 父级: ${city.parent_id})`);

          // 显示子区域
          const [children] = await connection.execute(
            'SELECT id, name, parent_id, extension FROM map_areas WHERE parent_id = ? ORDER BY id',
            [cityId]
          );

          children.forEach(child => {
            const childName = child.extension ? `${child.name}${child.extension}` : child.name;
            console.log(`    └─ ${childName} (ID: ${child.id})`);
          });
        }
      }
    }

    console.log('\n🎉 地图区域父ID关系更新完成！');

  } catch (error) {
    console.error('❌ 更新失败:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
  } finally {
    await connection.end();
    console.log('\n🔌 数据库连接已关闭');
  }
}

// 运行脚本
if (require.main === module) {
  updateMapAreasParentIds().catch(console.error);
}

module.exports = { updateMapAreasParentIds };
